package com.chris.webservice.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.Date;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.chris.webservice.entity.QueryInfoLog;

import javax.ejb.Stateless;

/**
 * 
 * @author chris
 */
@Stateless
public class LocationDao extends GenericMapJdbcDao {
	protected static Logger logger = LoggerFactory
    .getLogger(LocationDao.class);

	public int checkUser(String userName, String password) {
		int id = 0;
		ResultSet rs = null;
		Statement statement = null;
		Connection connection = null;
		try {
			
			String sql = "select * from Location_User_Manager t where t.UserName = '" + userName + "' and t.Password = '" + password + "' and t.privilege = 0";
			
			connection = super.getConnection();
			statement = connection.createStatement();
			rs = statement.executeQuery(sql);

			logger.debug(sql);
			
			while (rs != null && rs.next()) {
				id = rs.getInt("ID");
			}
		} catch (SQLException ex) {
			logger.error(ex.getMessage());
			return id;
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException ex) {
					logger.error(ex.getMessage());
					return id;
				}
			}
			super.closeStatement(statement);
			super.closeConnection(connection);
		}
		return id;
	}
	
	public void addLog(QueryInfoLog queryInfoLog) {
		//先检查该月的日志表是否存在，如果不存在创建该月日志表
		String tableName = this.checkQueryInfoLog();
		if (tableName.equals("")) {
			return;
		}
		
		//检查queryInfoLog字段是否有null数据
		if (queryInfoLog == null) {
			return;
		}
		if (queryInfoLog.getRequestContent() == null) {
			queryInfoLog.setRequestContent("");
		}
		if (queryInfoLog.getReturnContent() == null) {
			queryInfoLog.setReturnContent("");
		}
		
		Statement statement = null;
		Connection connection = null;
		try {
			
			String sql = "insert into " + tableName + "(RequestID, RequestTime,RequestContent,ReturnContent,ReturnCode,ReturnTime,ReturnID) " +
						 "values (" + queryInfoLog.getRequestID() + ", '" + queryInfoLog.getRequestTime() + "', '" + queryInfoLog.getRequestContent() + "', " +
						 "'" + queryInfoLog.getReturnContent() + "'," + queryInfoLog.getReturnCode() + ", '" + queryInfoLog.getReturnTime() + "', " + queryInfoLog.getReturnID() + ")";
			
			connection = super.getConnection();
			statement = connection.createStatement();
			statement.executeUpdate(sql);

			System.out.println(sql);
		} catch (SQLException ex) {
			logger.error(ex.getMessage());
		} finally {
			super.closeStatement(statement);
			super.closeConnection(connection);
		}
		
	}
	
	private String checkQueryInfoLog() {
		String resultTableName = "";
		
		//获取当前系统时间
		Date sysDate = new Date();
		
		//数据库表名Query_Info_Log_year4_month2
		SimpleDateFormat formatDate = new SimpleDateFormat("_yyyy_MM");
		String tableName = "Query_Info_Log" + formatDate.format(sysDate);
		
		ResultSet rs = null;
		Statement statement = null;
		Connection connection = null;
		try {
			
			String sql = "select * from sysobjects where name ='" + tableName + "' and xtype='u'";
			
			connection = super.getConnection();
			statement = connection.createStatement();
			rs = statement.executeQuery(sql);

			logger.debug(sql);
			
			//假如不存在创建该表
			if (rs == null || !rs.next()) {
				String createSQL = "USE [WZW_Location_Platform] " +
								   "SET ANSI_NULLS ON " +
								   "SET QUOTED_IDENTIFIER ON " +
								   "SET ANSI_PADDING ON " +
								   "CREATE TABLE [dbo].[" + tableName + "]( " +
								   "[ID] [int] IDENTITY(1, 1)  PRIMARY KEY, " +
								   "[RequestID] [smallint] NOT NULL, " +
								   "[RequestTime] [datetime] NOT NULL, " +
								   "[RequestContent] [varchar](1024) NOT NULL, " +
								   "[ReturnContent] [varchar](1024) NOT NULL, " +
								   "[ReturnCode] [tinyint] NOT NULL, " +
								   "[ReturnTime] [datetime] NOT NULL, " +
								   "[ReturnID] [nchar](10) NOT NULL " +
								   ") ON [PRIMARY] " +
								   "SET ANSI_PADDING OFF";
				logger.debug(createSQL);
				statement.execute(createSQL);
			}
			
			resultTableName = tableName;
		} catch (SQLException ex) {
			logger.error(ex.getMessage());
			return resultTableName;
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException ex) {
					logger.error(ex.getMessage());
					return resultTableName;
				}
			}
			super.closeStatement(statement);
			super.closeConnection(connection);
		}
		
		return resultTableName;
	}
}
